//
//  SQLiteDataHandel.m
//  UIkitSQLlite
//
//  Created by 李佳骏 on 15/7/3.
//  Copyright (c) 2015年 J.Lee. All rights reserved.
//

#import "SQLiteDataHandel.h"
//#import "Detail.h"

@implementation SQLiteDataHandel

static SQLiteDataHandel *SQh = nil;

+ (SQLiteDataHandel *)shareSQLiteDataHandle {
    
    //加上线程锁 保证访问安全
    @synchronized(self) {
        if (SQh == nil) {
        SQh = [[SQLiteDataHandel alloc] init];
        return SQh;
        }
    return SQh;
    }
}

//声明一个数据库指针 使用这个对象操作数据库
static sqlite3 *sq = nil;

- (void)openSQLite {
    //1. 找到SQLite的路径
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqlitePath = [documentPath stringByAppendingPathComponent:@"detail.sqlite"];
    
    //2. 创建数据表
    //打开数据库
    int openResult = sqlite3_open([sqlitePath UTF8String], &sq);
    
    //如果打开数据库失败 直接跳出
    if (openResult != SQLITE_OK) {
        NSLog(@"打开数据库失败");
        return;
    }
    
    //建表
    NSString *creatSQL = @"create table detail(detail_category text primary key not NULL,detail_jsonData BLOB not NULL)";
    //运行SQL语句
    
    char *error = nil;
    int result = sqlite3_exec(sq, [creatSQL UTF8String], NULL, NULL, &error);
    //3. 判断表是否创建成功
    if (result == SQLITE_OK) {
        NSLog(@"数据表创建成功");
    } else {
        NSLog(@"数据表创建失败: %s", error);
    }
    
}

#pragma mark - 关闭数据库 -
- (void)closeSQLite {
    int result = sqlite3_close(sq);
    if (result == SQLITE_OK) {
        sq = nil;
    }

}

#pragma mark - 插入用户信息 -
- (void)insertWithData:(NSData *)data category:(NSString *)category succesBlock:(insertMovieDetailSuccess)successBlock errorBlock:(insertMovieDetailError)errorBlock {

    
    self.successBlock = successBlock;
    self.errorBlock = errorBlock;
    
    //1. 插入数据的SQL语句 insert into 表名(字段1, 字段2...) values('值1', '值2'...)
    NSString *insertSQL = [NSString stringWithFormat:@"insert into detail (detail_category, detail_jsonData) values ('%@', '%@')", category, data];
    //2. 执行
    char *error = nil;
    int result = sqlite3_exec(sq, [insertSQL UTF8String], nil, nil, &error);
    //3. 判断
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
        self.successBlock();
        
    } else {
        NSLog(@"插入失败: %s", error);
        self.errorBlock([NSString stringWithCString:error encoding:NSUTF8StringEncoding]);
    }
    
}


#pragma mark - 查询所有用户信息 -
- (NSArray *)selectAllDetail {
    //1. 查询的SQL语句 当我们按照条件去查询表中的数据的时 SQL语句的格式是 select *from 表名 where 字段1 = '值1' and 字段2 = '值2'
    NSString *selectSQL = @"select * from detail";
    //2. 创建一个跟随的指针
    sqlite3_stmt *stm = nil;
    //3. 执行SQL语句
    int result = sqlite3_prepare_v2(sq, [selectSQL UTF8String], -1, &stm, nil);
    //4. 若执行成功 则取值
    NSMutableArray *userArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //通过循环取值
        while (sqlite3_step(stm) == SQLITE_ROW) {
            //取值 当我们没有自增ID 数据表会给我们生成一个rowID字段 但是这个rowID字段没有计算在表的字段内
            char *sqlTitle = (char *)sqlite3_column_text(stm, 0);
            char *sqlData = (char *)sqlite3_column_blob(stm, 1);
            
            
            //创建user对象
            NSString *category = [NSString stringWithUTF8String:sqlTitle];
            int size=sqlite3_column_bytes(stm, 1);
            NSData *data = [NSData dataWithBytes:sqlData length:size];
            NSDictionary *dic = [NSDictionary dictionaryWithObjectsAndKeys:data, category, nil];


            [userArray addObject:dic];
        }
        //释放指针
        sqlite3_finalize(stm);
        return userArray;
    } else {
        NSLog(@"执行错误");
    }
    return userArray;
}

- (NSDictionary *)selectDetailWithCategory:(NSString *)category {
    
    NSString *selectSQL=[NSString stringWithFormat:@"select *from detail where detail_category='%@'",category];
    
    sqlite3_stmt *stm=nil;
    
    int result=sqlite3_prepare_v2(sq, [selectSQL UTF8String], -1, &stm, nil);
    
    NSDictionary *dic = [NSDictionary dictionary];
    if (result==SQLITE_OK) {
        
        while (sqlite3_step(stm)==SQLITE_ROW) {
            char *sqlTitle = (char *)sqlite3_column_text(stm, 0);
            const void *sqlData = (void *)sqlite3_column_blob(stm, 1);
            
            NSString *category = [NSString stringWithUTF8String:sqlTitle];
            int size=sqlite3_column_bytes(stm, 1);
            
//            NSData *data = [NSData dataWithBytes:sqlData length:size];
            
            
//            data = [NSJSONSerialization JSONObjectWithData:data options:NSJSONReadingMutableContainers error:nil];
            
            NSData* data = [[NSData alloc] initWithBytes:sqlite3_column_blob(stm, 1) length: sqlite3_column_bytes(stm, 1)];
            
            NSDictionary *dic = [NSDictionary dictionaryWithObjectsAndKeys:data, category, nil];
            return dic;
        }
    }
    else{
        NSLog(@"查询错误");
    }
    
    sqlite3_finalize(stm);
    return dic;
    
}

#pragma mark - 根据姓名修改用户信息 -
- (void)updateDetailWithCategory:(NSString *)category data:(NSData *)data {
    //1. 修改的SQL语句 格式是 update 表名 set 字段1 = '值1', 字段2 = '值2' where 字段 = '值'
    NSString *updateSQL = [NSString stringWithFormat:@"update detail set detail_category = '%@' , detail_jsonData = '%@' where detail_category = '%@'", category, data, category];
    //2. 执行语句
    char *error = nil;
    int result = sqlite3_exec(sq, [updateSQL UTF8String], nil, nil, &error);
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    } else {
        NSLog(@"修改失败: %s", error);
    }
}
















@end
